package com.citytransport.prokopjuk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;

import org.apache.log4j.Logger;

import com.citytransport.prokopjuk.connectDB.ConnectionPool;
import com.citytransport.prokopjuk.vehicleDTO.Trolleybus;

public class DAOTrolleybus implements IDAOTrolleybus {
    private ConnectionPool pool;
    private DAOVehicle daoVehicle;
    static Logger logger = Logger.getLogger(DAOTrolleybus.class.getName());

    public DAOTrolleybus() {
	pool = ConnectionPool.getConnectionPool();
	daoVehicle = new DAOVehicle();
    }

    public void create(Trolleybus trolleybus) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	daoVehicle.create(trolleybus);
	try {
	    prepStmt = con
		    .prepareStatement("insert into trolleybus values(?, ?)");
	    prepStmt.setInt(1, trolleybus.getID());
	    prepStmt.setInt(2, trolleybus.getElectricVolatge());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
    }

    public void delete(Trolleybus trolleybus) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con
		    .prepareStatement("delete from trolleybus where trolleybusID = ?");
	    prepStmt.setInt(1, trolleybus.getID());
	    prepStmt.executeUpdate();
	    daoVehicle.delete(trolleybus);
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
    }

    public LinkedList<Integer> getAllTrolleybusID() {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	ResultSet rezSet = null;
	LinkedList<Integer> index = new LinkedList<Integer>();
	try {
	    prepStmt = con
		    .prepareStatement("select trolleybusID from trolleybus");
	    rezSet = prepStmt.executeQuery();
	    while (rezSet.next()) {
		index.add(rezSet.getInt("trolleybusID"));
	    }
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(con);
	}
	return index;
    }

    public void deleteAll() {
	Connection con = pool.getConnection();
	try {
	    Statement stmt = con.createStatement();
	    stmt.executeUpdate("delete from trolleybus");
	} catch (SQLException e1) {
	    logger.error(e1);
	} finally {
	    pool.returnConnecction(con);
	}
	LinkedList<Integer> index = new LinkedList<Integer>();
	index = getAllTrolleybusID();
	for (int e : index) {
	    daoVehicle.delete(e);
	}
    }

    public Trolleybus read(int trolleybusID) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	Trolleybus trolleybus = null;
	try {
	    prepStmt = con
		    .prepareStatement("select * from trolleybus where trolleybusID = ?");
	    prepStmt.setInt(1, trolleybusID);
	    ResultSet rezSet = prepStmt.executeQuery();
	    HashMap<String, String> atr = new HashMap<String, String>();
	    atr = daoVehicle.read(trolleybusID);
	    rezSet.next();
	    trolleybus = new Trolleybus(Integer.parseInt(atr.get("vehicleID")),
		    Integer.parseInt(atr.get("seatAmount")), atr.get("registrationNumbes"), 
		    atr.get("model"),rezSet.getInt("ELECTRICVOLATGE"));
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	}
	return trolleybus;
    }

    public ArrayList<Trolleybus> readAll() {
	LinkedList<Integer> index = new LinkedList<Integer>();
	index = getAllTrolleybusID();
	ArrayList<Trolleybus> trList = new ArrayList<Trolleybus>();
	for(int e: index){
	    trList.add(read(e));
	}
	return trList;
    }

    public void update(Trolleybus trolleybus, int seatAmount,
	    String registrationNumber, String model, int electricVolatge, int routeID) {
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	try {
	    prepStmt = con.prepareStatement("update trolleybus set ELECTRICVOLATGE  = ? where trolleybusID = ?");
	    prepStmt.setInt(1, electricVolatge);
	    prepStmt.setInt(2, trolleybus.getID());
	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	}
	finally{
	    pool.returnConnecction(con);
	}
	daoVehicle.update(trolleybus, seatAmount, registrationNumber, model, routeID);
    }
    
    private LinkedList<Integer> getTrolleybusIDAtRoute(int routeID){
	LinkedList<Integer> index = new LinkedList<Integer>();
	Connection con = pool.getConnection();
	try {
	    PreparedStatement prepStmt= con.prepareStatement(
		    "Select TrolleybusID from Trolleybus where TrolleybusID in " +
		"(select vehicleID from vehicle where routeID = ?)");;
		prepStmt.setInt(1, routeID);
	    ResultSet resSet = prepStmt.executeQuery();
	    while(resSet.next()){
		index.add(resSet.getInt("TrolleybusID"));
	    }	    
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);	    
	}
	
	return index;
    }
    
    public ArrayList<Trolleybus> readAtRoute(int routeID) {
	LinkedList<Integer> a = new LinkedList<Integer>(getTrolleybusIDAtRoute(routeID));
	ArrayList<Trolleybus> trolleybuses = new ArrayList<Trolleybus>();
	for(int index: a){
	    trolleybuses.add(read(index));
	}	
	return trolleybuses;
    }
}
